<?php
ob_start();
session_start();
include_once($_SERVER['DOCUMENT_ROOT'].'/htqlmba/configs/database.php');
/*

  /* Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

if (PHP_SAPI == 'cli')
    die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once($_SERVER['DOCUMENT_ROOT'] . '/htqlmba/libraries/PHPExcel_1.7.9_doc/Classes/PHPExcel.php');

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
        ->setLastModifiedBy("Maarten Balliauw")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");


$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', 'Công Ty Điện Lực Cà Mau');


$objPHPExcel->getActiveSheet()->getStyle('A1:C1')->applyFromArray(
        array(
            'font' => array(
                'size' => 15,
                'name' => 'Times new Roman',
                'bold' => true, 'italic' => true
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            )
        )
);
$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('G1', 'CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM');
$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('G2', 'Độc Lập - Tự Do - Hạnh phúc');		
		//format quốc ngữ
$objPHPExcel->getActiveSheet()->getStyle('G1:H1')->applyFromArray(
        array(
            'font' => array(
                'size' => 16,
                'name' => 'Times new Roman',
                'bold' => true
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            )
        )
);
$objPHPExcel->getActiveSheet()->getStyle('G2:H2')->applyFromArray(
        array(
            'font' => array(
                'size' => 16,
                'name' => 'Times new Roman',
                'bold' => true
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            )
        )
);
$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A2', 'Địa chỉ: 22 Ngô Quyền P2 TP Cà Mau');
$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('C2', 'Ngày Tạo: ' . date('d/m/Y', time()));
$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A3', 'Liên hệ: Nguyễn Phạm Thanh Tú/CBKT/Phòng KH-KT');
$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A4', 'Điện thoại/Fax/Email: ');
$objPHPExcel->getActiveSheet()->getStyle('A2:C4')->applyFromArray(
        array(
            'alignment' => array(
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
            )
        )
);

  $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A6', 'Thông tin cơ bản')
					->setCellValue('A7', 'STT')	
                    ->setCellValue('B7', 'Số N0')				
                    ->setCellValue('C7', 'MSTS')				
                    ->setCellValue('D7', 'Loại điện áp(U)')					
					->setCellValue('E7', 'Công Suất(KVA)')					
					->setCellValue('F7', 'Chiều dài(mm)')					
					->setCellValue('G7', 'Chiều rộng(mm)')					
					->setCellValue('H7', 'Chiều cao(mm)')					
					->setCellValue('I7', 'Đầu máy(kg)')
					->setCellValue('J7', 'Ruột máy(kg)')
					->setCellValue('K7', 'Tổng(kg)')
					->setCellValue('L7', 'Hãng sản xuất')
					->setCellValue('M7', 'Nước sản xuất')
					->setCellValue('N7', 'Loại dầu')
					->setCellValue('O7', 'Thông số đo')
					->setCellValue('P7', 'Năm sản xuất')
					->setCellValue('Q7', 'Năm nhập về')
					->setCellValue('R7', 'Đơn vị hiện đang sở hữu')
					->setCellValue('S7', 'Trạm')
					->setCellValue('T7', 'Ngày vận hành')
					->setCellValue('U7', 'Tình trạng máy')
					->setCellValue('V7', 'Nhận xét')
					->setCellValue('W7', 'Ghi chú');

	//format title 'thông tin cơ bản'
	  $objPHPExcel->getActiveSheet()->getStyle('A6:A6')->applyFromArray(
                    array(
                        'font' => array(
							'bold' => true,
                            'size' => 16,							
                            'name' => 'Times new Roman'
							
                        ),           
                        'alignment' => array(
                            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                        )
                    )
            );
	//Tô màu tên các cột
	$objPHPExcel->getActiveSheet()->getStyle('A7:W7')->applyFromArray(
                    array(
                        'font' => array(
                            'bold' => true,
                            'size' => 13,                          
                            'name' => 'Times new Roman'
                        ),
                         'borders' => array(
                            'allborders' => array(
                                'style' => PHPExcel_Style_Border::BORDER_THIN,                              
                            )
                        ),
                       
						 'alignment' => array(
                		'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                		'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
           				 )
                    )
            );	
	$from="may_bien_ap a";
		$where="";
		if($_SESSION["donvi"]=="" && $_SESSION["tram"]=="" && $_SESSION["tinhtrang"]==""){
			$where.="CONG_SUAT like '".$_SESSION["congsuat"]."'";
		}
		else if($_SESSION["donvi"]=="" && $_SESSION["tram"]==""){
			$from.=",chi_tiet_tt_may b";
			$where.="a.SO_N0=b.SO_N0 and CONG_SUAT like '".$_SESSION["congsuat"]."' and b.TEN_TINH_TRANG_MAY='".$_SESSION["tinhtrang"]."'";
		}
		else if($_SESSION["donvi"]=="" && $_SESSION["tinhtrang"]==""){
			$from.=",mba_tram b";
			$where.="a.SO_N0=b.SO_N0 and CONG_SUAT like '".$_SESSION["congsuat"]."'and b.MA_TRAM like '".$_SESSION["tram"]."'";
		}
		else if($_SESSION["tram"]=="" && $_SESSION["tinhtrang"]==""){
			$from.=",chi_tiet_so_huu b";
			$where.="a.SO_N0=b.SO_N0 and CONG_SUAT like '".$_SESSION["congsuat"]."'and b.MA_DON_VI like '".$_SESSION["donvi"]."'";
		}
		else if($_SESSION["donvi"]==""){
			$from.=",mba_tram b, chi_tiet_tt_may c";
			$where.="a.SO_N0=b.SO_N0 and a.SO_N0=c.SO_N0 and CONG_SUAT like '".$_SESSION["congsuat"]."' and b.MA_TRAM like '".$_SESSION["tram"]."' and c.TEN_TINH_TRANG_MAY='".$_SESSION["tinhtrang"]."'";			
		}
		else if($_SESSION["tram"]==""){
			$from.=",chi_tiet_so_huu b, chi_tiet_tt_may c";
			$where.="a.SO_N0=b.SO_N0 and a.SO_N0=c.SO_N0 and CONG_SUAT like '".$_SESSION["congsuat"]."' and b.MA_DON_VI like '".$_SESSION["donvi"]."' and c.TEN_TINH_TRANG_MAY ='".$_SESSION["tinhtrang"]."'";
		}
		else if($_SESSION["tinhtrang"]==""){
			$from.=",chi_tiet_so_huu b, mba_tram c";
			$where.="a.SO_N0=b.SO_N0 and a.SO_N0=c.SO_N0 and CONG_SUAT like '".$_SESSION["congsuat"]."' and b.MA_DON_VI like '".$_SESSION["donvi"]."' and c.MA_TRAM= '".$_SESSION["tram"]."'";
		}
		else{
			$from.=",chi_tiet_so_huu b, mba_tram c, chi_tiet_tt_may d";
			$where.="a.SO_N0=b.SO_N0 and a.SO_N0=c.SO_N0 and a.SO_N0 = d.SO_N0 and CONG_SUAT like '".$_SESSION["congsuat"]."' and b.MA_DON_VI like '".$_SESSION["donvi"]."' and c.MA_TRAM like '".$_SESSION["tram"]."' and d.TEN_TINH_TRANG_MAY='".$_SESSION["tinhtrang"]."'";
		}
	$stt=1;
	$line=8;	
	$db = new database();
    $db->setQuery("select distinct a.SO_N0,MSTS, e.CAP_DIEN_AP,TEN_LOAI_DAU,CONG_SUAT,CHIEU_DAI,CHIEU_RONG,CHIEU_CAO,DAU_MAY,RUOT_MAY,TONG,TEN_NSX,TEN_NUOC,THONG_SO_DO, NAM_SX, NAM_NHAP_VE,NHAN_XET,GHI_CHU from ".$from.",loai_dien_ap e, nha_sx f where a.MA_DIEN_AP=e.MA_DIEN_AP and a.MA_NSX=f.MA_NSX and ".$where);
    $result=$db->fetchAll();	
				
			while($row=mysql_fetch_array($result)){
				 $db->setQuery("Select distinct a.MA_DON_VI, TEN_DON_VI from chi_tiet_so_huu a, don_vi b where a.MA_DON_VI=b.MA_DON_VI and a.SO_N0='".$row['SO_N0']."' and NGAY_CHUYEN_DEN=(select max(NGAY_CHUYEN_DEN) from chi_tiet_so_huu where SO_N0='".$row['SO_N0']."')");			
				$result2=$db->fetchAll();				
				$row2=mysql_fetch_array($result2);						
				$donvi=$row2["MA_DON_VI"];				  
				
				//lay ten tram co ngay van hanh gan nhat	
				 $db->setQuery("select distinct a.MA_TRAM, TEN_TRAM,NGAY_VAN_HANH from mba_tram a, tram b where a.MA_TRAM=b.MA_TRAM and SO_N0='".$row['SO_N0']."' and NGAY_VAN_HANH=(select max(NGAY_VAN_HANH) from mba_tram where SO_N0='".$row['SO_N0']."')");				
				$result3=$db->fetchAll();				
				$row3=mysql_fetch_array($result3);
				$tram=$row3["MA_TRAM"];
				
				//lay ten tinh trang gan day nhat
				$db->setQuery("select distinct TEN_TINH_TRANG_MAY from chi_tiet_tt_may where SO_N0='".$row['SO_N0']."' and TINH_TRANG_NGAY=(select max(TINH_TRANG_NGAY) from chi_tiet_tt_may where SO_N0='".$row['SO_N0']."')");
				$result0=$db->fetchAll();				
				$row0=mysql_fetch_array($result0);						
				$tinhtrang=$row0["TEN_TINH_TRANG_MAY"];
					
				if($_SESSION["donvi"]==""){
					if(($_SESSION["tram"]=="")||($_SESSION["tram"]!="" && $tram==$_SESSION["tram"])){	
						if(($_SESSION["tinhtrang"]=="")||($_SESSION["tinhtrang"]!="" && $tinhtrang==$_SESSION["tinhtrang"])){
						 $objPHPExcel->setActiveSheetIndex(0)
							->setCellValue("A".$line, $stt)							
							->setCellValue("B".$line, $row["SO_N0"])				
							->setCellValue("C".$line, $row["MSTS"])				
							->setCellValue("D".$line, $row["CAP_DIEN_AP"])					
							->setCellValue("E".$line, $row["CONG_SUAT"])					
							->setCellValue("F".$line, $row["CHIEU_DAI"])
							->setCellValue("G".$line, $row["CHIEU_RONG"])
							->setCellValue("H".$line, $row["CHIEU_CAO"])
							->setCellValue("I".$line, $row["DAU_MAY"])
							->setCellValue("J".$line, $row["RUOT_MAY"])
							->setCellValue("K".$line, $row["TONG"])
							->setCellValue("L".$line, $row["TEN_NSX"])
							->setCellValue("M".$line, $row["TEN_NUOC"])
							->setCellValue("N".$line, $row["TEN_LOAI_DAU"])
							->setCellValue("O".$line, $row["THONG_SO_DO"])
							->setCellValue("P".$line, $row["NAM_SX"])
							->setCellValue("Q".$line, $row["NAM_NHAP_VE"])
							->setCellValue("R".$line, $row2["TEN_DON_VI"])
							->setCellValue("S".$line, $row3["TEN_TRAM"])
							->setCellValue("T".$line, $row3["NGAY_VAN_HANH"])
							->setCellValue("U".$line, $row0["TEN_TINH_TRANG_MAY"])
							->setCellValue("V".$line, $row["NHAN_XET"])
							->setCellValue("W".$line, $row["GHI_CHU"]);
							
							/*	//Tô màu dữ liệu
							
								$objPHPExcel->getActiveSheet()->getStyle("A".$line.":w".$line)->applyFromArray(
									array(	
								  	'font' => array(									
									'size' => 12,                         
									'name' => 'Times new Roman'
										),
								 	 'borders' => array(
									'allborders' => array(
										'style' => PHPExcel_Style_Border::BORDER_THIN,
									
									 )
										),
								 
									'alignment' => array(
									'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
									'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
										)
									)
								);	*/		
						
							//Tô màu STT
							$objPHPExcel->getActiveSheet()->getStyle("A".$line)->applyFromArray(
                    array(
                        'font' => array(
                            'bold' => true,
                            'size' => 13,               
                            'name' => 'Times new Roman'
                        ),
                         'borders' => array(
                            'allborders' => array(
                                'style' => PHPExcel_Style_Border::BORDER_THIN,                      
                            )
                        ),
                      
						 'alignment' => array(
                		'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                		'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
           				 )
                    )
            );	
							$stt++;	
							$line+=1;;		
											
						}
					}
				}
				else if($_SESSION["donvi"]!=""){
									
					if(($_SESSION["tram"]=="" && $donvi==$_SESSION["donvi"])||($_SESSION["tram"]!="" && $donvi==$_SESSION["donvi"] && $tram==$_SESSION["tram"])){				
						if(($_SESSION["tinhtrang"]=="")||($_SESSION["tinhtrang"]!="" && $tinhtrang==$_SESSION["tinhtrang"])){
						  $objPHPExcel->setActiveSheetIndex(0)
							->setCellValue("A".$line, $stt)							
							->setCellValue("B".$line, $row["SO_N0"])				
							->setCellValue("C".$line, $row["MSTS"])				
							->setCellValue("D".$line, $row["CAP_DIEN_AP"])					
							->setCellValue("E".$line, $row["CONG_SUAT"])					
							->setCellValue("F".$line, $row["CHIEU_DAI"])
							->setCellValue("G".$line, $row["CHIEU_RONG"])
							->setCellValue("H".$line, $row["CHIEU_CAO"])
							->setCellValue("I".$line, $row["DAU_MAY"])
							->setCellValue("J".$line, $row["RUOT_MAY"])
							->setCellValue("K".$line, $row["TONG"])
							->setCellValue("L".$line, $row["TEN_NSX"])
							->setCellValue("M".$line, $row["TEN_NUOC"])
							->setCellValue("N".$line, $row["TEN_LOAI_DAU"])
							->setCellValue("O".$line, $row["THONG_SO_DO"])
							->setCellValue("P".$line, $row["NAM_SX"])
							->setCellValue("Q".$line, $row["NAM_NHAP_VE"])
							->setCellValue("R".$line, $row2["TEN_DON_VI"])
							->setCellValue("S".$line, $row3["TEN_TRAM"])
							->setCellValue("T".$line, $row3["NGAY_VAN_HANH"])
							->setCellValue("U".$line, $row0["TEN_TINH_TRANG_MAY"])
							->setCellValue("V".$line, $row["NHAN_XET"])
							->setCellValue("W".$line, $row["GHI_CHU"]);
						
						/*	//Tô màu dữ liệu
							
							
								$objPHPExcel->getActiveSheet()->getStyle("A".$line.":w".$line)->applyFromArray(
									array(	
								  	'font' => array(									
									'size' => 12,                         
									'name' => 'Times new Roman'
										),
								 	 'borders' => array(
									'allborders' => array(
										'style' => PHPExcel_Style_Border::BORDER_THIN,
										
									 )
										),
								 	
									'alignment' => array(
									'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
									'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
										)
									)
								);		*/	
							
								//Tô màu STT
							$objPHPExcel->getActiveSheet()->getStyle("A".$line)->applyFromArray(
                    array(
                        'font' => array(
                            'bold' => true,
                            'size' => 13,                            
                            'name' => 'Times new Roman'
                        ),
                         'borders' => array(
                            'allborders' => array(
                                'style' => PHPExcel_Style_Border::BORDER_THIN,                               
                            )
                        ),
                        
						 'alignment' => array(
                		'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                		'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
           				 )
                    )
            );	
							$stt++;	
							$line+=1;			
						}
					}
					
				}
				
				
			}
		
		
	
//column size
	// test change
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
	
	// end test change 
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);	
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(16);	
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(17);	
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(16);	
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(17);
	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(16);
	$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(16);
	$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(16);
	$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(16);
	$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);	
	$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
	$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);	
	$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(14);	
	$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(30);	
	$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(30);	
	$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(15);	
	$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(16);	
	$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(20);	
	$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(20);	
	
	$test_highest_row_1=$objPHPExcel->getActiveSheet()->getHighestRow();
							
								//Tô màu dữ liệu
							
								$objPHPExcel->getActiveSheet()->getStyle("A8".":W".$test_highest_row_1)->applyFromArray(
									array(	
								  	'font' => array(									
									'size' => 12,                         
									'name' => 'Times new Roman'
										),
								 	 'borders' => array(
									'allborders' => array(
										'style' => PHPExcel_Style_Border::BORDER_THIN,
									
									 )
										),
								 
									'alignment' => array(
									'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
									'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
										)
									)
								);	
//merge 
  $objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
  $objPHPExcel->getActiveSheet()->mergeCells('G1:J1');
  $objPHPExcel->getActiveSheet()->mergeCells('G2:J2');
  $objPHPExcel->getActiveSheet()->mergeCells('A6:W6');





// &*&*&**&* HET TEST THEM
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Kết quả tìm kiếm nâng cao');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="kqtimkiemnc.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>